﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    public class KbArticleBase
    {
        public KbArticleBase() { }
  
        public string Add(Model.KbArticleBase __news)
        {
            string result = "";

            string _articleId = System.Guid.NewGuid().ToString();
            string sqlstring = "Insert Into [KbArticleBase] (ArticleId,CategoryId,TypeCode,Title,Keyword,Content,Author,Source,Copyright,Resume,IsTop,IsShowIndex,IsApproved,StatusCode,CreatedBy,TitlePic,NewsURL,SourceType,ArticleNumber,PublishDate)";
            sqlstring += "Values(@ArticleId, @CategoryId, @TypeCode, @Title, @Keyword, @Content, @Author, @Source, @Copyright, @Resume, @IsTop, @IsShowIndex, @IsApproved, @StatusCode, @CreatedBy,@TitlePic,@NewsURL,@SourceType,@ArticleNumber,@PublishDate)";
            SqlParameter[] prams = {
						SqlHelper.CreateInputParam("@ArticleId", SqlDbType.UniqueIdentifier, new Guid(_articleId)),
						SqlHelper.CreateInputParam("@CategoryId", SqlDbType.UniqueIdentifier, new Guid(__news.CategoryId)),
						SqlHelper.CreateInputParam("@TypeCode", SqlDbType.Int, __news.TypeCode),
						SqlHelper.CreateInputParam("@Title", SqlDbType.NVarChar, __news.Title),
						SqlHelper.CreateInputParam("@Keyword", SqlDbType.NVarChar, __news.Keyword),
						SqlHelper.CreateInputParam("@Content", SqlDbType.NVarChar, __news.Content),
						SqlHelper.CreateInputParam("@Author", SqlDbType.NVarChar, __news.Author),
                        SqlHelper.CreateInputParam("@Source", SqlDbType.NVarChar, __news.Source),
						SqlHelper.CreateInputParam("@Copyright", SqlDbType.NVarChar, __news.Copyright),
						SqlHelper.CreateInputParam("@Resume", SqlDbType.NVarChar, __news.Resume),
                        SqlHelper.CreateInputParam("@IsTop", SqlDbType.Bit, __news.IsTop),
                        SqlHelper.CreateInputParam("@IsShowIndex", SqlDbType.Bit, __news.IsShowIndex),
                        SqlHelper.CreateInputParam("@IsApproved", SqlDbType.Bit, __news.IsApproved),
                        SqlHelper.CreateInputParam("@StatusCode", SqlDbType.SmallInt, __news.StatusCode),
                        SqlHelper.CreateInputParam("@CreatedBy", SqlDbType.UniqueIdentifier, new Guid(__news.CreatedBy)),
                        SqlHelper.CreateInputParam("@TitlePic",SqlDbType.VarChar,__news.TitlePic),
                        SqlHelper.CreateInputParam("@NewsURL",SqlDbType.VarChar,__news.NewsURL),
                        SqlHelper.CreateInputParam("@SourceType",SqlDbType.Int,__news.SourceType),
                        SqlHelper.CreateInputParam("@ArticleNumber",SqlDbType.Decimal,__news.ArticleNumber),
                        SqlHelper.CreateInputParam("@PublishDate",SqlDbType.DateTime,__news.PublishDate)
			};

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstring, prams);
                result = "succeeded|" + _articleId;
            }
            catch (Exception ex)
            {
                result = ex.ToString();
            }

            return result;
        }

        public string Update(Model.KbArticleBase __news)
        {
            string result = "";

            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    string sqlstring = "UPDATE [KbArticleBase] SET CategoryId = @CategoryId, Title = @Title, Keyword = @Keyword, Content = @Content, Author = @Author, Source = @Source, Copyright = @Copyright, Resume = @Resume, IsTop = @IsTop, IsShowIndex = @IsShowIndex, StatusCode = @StatusCode, ModifiedBy = @ModifiedBy, ModifiedOn = getdate(), TitlePic = @TitlePic, NewsURL = @NewsURL, SourceType = @SourceType, ArticleNumber = @ArticleNumber, PublishDate = @PublishDate Where ArticleId = @ArticleId";
                    SqlParameter[] prams = {
						SqlHelper.CreateInputParam("@ArticleId", SqlDbType.UniqueIdentifier, new Guid(__news.ArticleId)),
						SqlHelper.CreateInputParam("@CategoryId", SqlDbType.UniqueIdentifier, new Guid(__news.CategoryId)),
						SqlHelper.CreateInputParam("@TypeCode", SqlDbType.Int, __news.TypeCode),
						SqlHelper.CreateInputParam("@Title", SqlDbType.NVarChar, __news.Title),
						SqlHelper.CreateInputParam("@Keyword", SqlDbType.NVarChar, __news.Keyword),
						SqlHelper.CreateInputParam("@Content", SqlDbType.NVarChar, __news.Content),
						SqlHelper.CreateInputParam("@Author", SqlDbType.NVarChar, __news.Author),
                        SqlHelper.CreateInputParam("@Source", SqlDbType.NVarChar, __news.Source),
						SqlHelper.CreateInputParam("@Copyright", SqlDbType.NVarChar, __news.Copyright),
						SqlHelper.CreateInputParam("@Resume", SqlDbType.NVarChar, __news.Resume),
                        SqlHelper.CreateInputParam("@IsTop", SqlDbType.Bit, __news.IsTop),
                        SqlHelper.CreateInputParam("@IsShowIndex", SqlDbType.Bit, __news.IsShowIndex),
                        SqlHelper.CreateInputParam("@IsApproved", SqlDbType.Bit, __news.IsApproved),
                        SqlHelper.CreateInputParam("@StatusCode", SqlDbType.SmallInt, __news.StatusCode),
                        SqlHelper.CreateInputParam("@ModifiedBy", SqlDbType.UniqueIdentifier, new Guid(__news.ModifiedBy)),
                        SqlHelper.CreateInputParam("@TitlePic",SqlDbType.VarChar,__news.TitlePic),
                        SqlHelper.CreateInputParam("@NewsURL",SqlDbType.VarChar,__news.NewsURL),
                        SqlHelper.CreateInputParam("@SourceType",SqlDbType.Int,__news.SourceType),
                        SqlHelper.CreateInputParam("@ArticleNumber",SqlDbType.Decimal,__news.ArticleNumber),
                        SqlHelper.CreateInputParam("@PublishDate",SqlDbType.DateTime,__news.PublishDate)
					};
 
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlstring, prams);

                    trans.Commit();
                    result = "succeeded|" + __news.ArticleId;
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();

            return result;
        }

        public Model.KbArticleBase GetObjById(string newsid)
        {
            Model.KbArticleBase __obj = null;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_KbArticleBase WHERE [ArticleId] = @ArticleId;");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ArticleId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(newsid);

            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                __obj = new Model.KbArticleBase();
                __obj.CategoryId = dr["CategoryId"].ToString();
                __obj.CategoryNumber = dr["CategoryNumber"].ToString();
                __obj.CategoryName = dr["CategoryName"].ToString();
                __obj.TypeCode = int.Parse(dr["TypeCode"].ToString());
                __obj.Title = dr["Title"].ToString();
                __obj.Keyword = dr["Keyword"].ToString();
                __obj.Content = dr["Content"].ToString();
                __obj.Author = dr["Author"].ToString();
                __obj.Source = dr["Source"].ToString();
                __obj.Copyright = dr["Copyright"].ToString();
                __obj.Resume = dr["Resume"].ToString();
                __obj.IsTop = (dr["IsTop"].ToString() == "True") ? true : false;
                __obj.IsApproved = (dr["IsApproved"].ToString() == "True") ? true : false;
                __obj.IsShowIndex = (dr["IsShowIndex"].ToString() == "True") ? true : false;
                __obj.StatusCode = int.Parse(dr["StatusCode"].ToString());
                __obj.CreatedBy = dr["CreatedBy"].ToString();
                __obj.CreatedOn = DateTime.Parse(dr["CreatedOn"].ToString());
                __obj.PublishDate = DateTime.Parse(dr["PublishDate"].ToString()); 
                __obj.ModifiedBy = dr["ModifiedBy"].ToString();
                __obj.ModifiedOn = DateTime.Parse(dr["ModifiedOn"].ToString()); 
                __obj.TitlePic = dr["TitlePic"].ToString();
                __obj.NewsURL = dr["NewsURL"].ToString();
                __obj.SourceType = Convert.ToInt32(dr["SourceType"].ToString());
                __obj.ArticleNumber = string.IsNullOrEmpty(dr["ArticleNumber"].ToString()) ? 0 : Decimal.Parse(dr["ArticleNumber"].ToString());
                __obj.Tags = dr["Tags"].ToString(); 
                __obj.Hits = long.Parse(dr["Hits"].ToString()); 

                dt.Dispose();
            }

            return __obj;
        } 

        public void Delete(string newsid)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ArticleId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(newsid);
 
            strSql.Append("DELETE FROM KbArticleBase WHERE [ArticleId] = @ArticleId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 置顶
        /// </summary>
        /// <param name="oid"></param>
        /// <param name="status"></param>
        public void SetIsTop(string oid, string status)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@ArticleId", SqlDbType.UniqueIdentifier),
                new SqlParameter("@IsTop",SqlDbType.Int)
            };
            parameters[0].Value = new Guid(oid);
            parameters[1].Value = Convert.ToInt32(status);

            strSql.Append("UPDATE KbArticleBase SET IsTop=@IsTop WHERE [ArticleId] = @ArticleId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新大于指定编号的记录，将其编号 + 1
        /// </summary>
        /// <param name="number"></param>
        static void UpdateArticleNumber(string number)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "SELECT ArticleId, ArticleNumber FROM [KbArticleBase] WHERE [ArticleNumber] >= " + number.Trim() + " ORDER BY ArticleNumber ASC");
            int num = 0;
            foreach (DataRow dr in dt.Select(""))
            {
                num = int.Parse(dr[1].ToString()) + 1;
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "Update vw_KbArticleBase SET ArticleNumber = " + num + " WHERE [ArticleId] = '" + dr[0].ToString().Trim() + "'");
            }
        }

        /// <summary>
        /// 更新大于指定编号的记录，将其编号 + 1
        /// </summary>
        /// <param name="number"></param>
        static void UpdateArticleNumber(string number, string categorynumber)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "Update vw_KbArticleBase SET ArticleNumber = ArticleNumber + 1 WHERE [ArticleNumber] >= " + number.Trim() + " AND CategoryNumber LIKE '" + categorynumber + "%'");
        }

        /// <summary>
        /// 判断数据库中是否已存在相同的编号
        /// 已存在 True, 不存在 False;
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        static bool ValidateExistingNumber(string number, string newsid, string categorynumber)
        {
            bool exist = false;
            object _obj = null;

            if (newsid != "")
            {
                _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "SELECT ArticleNumber FROM [KbArticleBase] WHERE [ArticleId] = '" + newsid.Trim() + "'");
                if (_obj != null)
                {
                    if (_obj.ToString() == number.Trim())
                        return false;
                    else
                    {
                        _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "SELECT COUNT(1) FROM [vw_KbArticleBase] WHERE [ArticleNumber] = " + number.Trim() + " AND CategoryNumber LIKE '" + categorynumber + "%'");
                        if (_obj != null)
                        {
                            if ((int)_obj > 0)
                                exist = true;
                        }
                    }
                }
            }

            return exist;
        }

        /// <summary>
        /// 更新文章点击次数
        /// </summary>
        public void UpdateHits(string newsid)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@ArticleId", SqlDbType.UniqueIdentifier)
            };
            parameters[0].Value = new Guid(newsid);

            strSql.Append("UPDATE KbArticleBase SET Hits = Hits + 1 WHERE [ArticleId] = @ArticleId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }
    }
}
